{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Links\n",
    "\n",
    "#### Gurobi AI Modeling Documentation\n",
    "https://gurobi-optimization-gurobi-ai-modeling.readthedocs-hosted.com/en/latest/\n",
    "\n",
    "#### Gurobi AI Modeling GPT\n",
    "https://chatgpt.com/g/g-g69cy3XAp-gurobi-ai-modeling-assistant\n",
    "\n",
    "This will work best if you have a *Plus* account, but should generate formulations and code without it. \n",
    "\n",
    "#### Source Data\n",
    "https://www.kaggle.com/datasets/pedroisrael/mix-marketing-analysis?select=Bathrooms_random_data2.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prompt Template\n",
    "\n",
    "Problem description:\n",
    "<describe the problem on a high level, making sure you also introduce the context. Normally, 3-5 sentences is sufficient for this.>\n",
    "\n",
    "Objective: <Maximize/Minimize> <objective>\n",
    "\n",
    "Constraints:\n",
    "- <constraint name 1>: <describe constraint>\n",
    "- <constraint name 2>: <describe constraint>\n",
    "- <constraint name 3>: <describe constraint>\n",
    "- etc.\n",
    "\n",
    "Data:\n",
    "<data in csv format, including headers> or \\<upload your files and name the filename here>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regression Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>slope</th>\n",
       "      <th>min_spend</th>\n",
       "      <th>max_spend</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>productcategory</th>\n",
       "      <th>mediatype</th>\n",
       "      <th>platform</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Other</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>Pinterest</th>\n",
       "      <td>91.6273</td>\n",
       "      <td>0.0</td>\n",
       "      <td>474</td>\n",
       "      <td>9646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>82.2761</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10909</td>\n",
       "      <td>10796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bing Search</th>\n",
       "      <th>Bing</th>\n",
       "      <td>3.3551</td>\n",
       "      <td>1.0</td>\n",
       "      <td>541</td>\n",
       "      <td>5219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Programmatic Video</th>\n",
       "      <th>discovery</th>\n",
       "      <td>36.5491</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2142</td>\n",
       "      <td>1692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Programmatic Display</th>\n",
       "      <th>discovery</th>\n",
       "      <td>27.4809</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22421</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>padsquad</th>\n",
       "      <td>68.5564</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3729</td>\n",
       "      <td>2534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>the trade desk</th>\n",
       "      <td>220.2158</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4145</td>\n",
       "      <td>1575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kargo</th>\n",
       "      <td>124.7605</td>\n",
       "      <td>1.0</td>\n",
       "      <td>564</td>\n",
       "      <td>1060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Toilets</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>Pinterest</th>\n",
       "      <td>69.8756</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1252</td>\n",
       "      <td>5596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>124.2930</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3983</td>\n",
       "      <td>18698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bing Search</th>\n",
       "      <th>Bing</th>\n",
       "      <td>123.4833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1632</td>\n",
       "      <td>6526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Direct Display</th>\n",
       "      <th>new york times_pg</th>\n",
       "      <td>51.9730</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5192</td>\n",
       "      <td>3477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uncrate</th>\n",
       "      <td>50.0850</td>\n",
       "      <td>0.0</td>\n",
       "      <td>957</td>\n",
       "      <td>411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">OOH</th>\n",
       "      <th>big outdoor</th>\n",
       "      <td>65.7552</td>\n",
       "      <td>846.7</td>\n",
       "      <td>23044</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>clear channel</th>\n",
       "      <td>57.7892</td>\n",
       "      <td>49888.8</td>\n",
       "      <td>67539</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>National TV</th>\n",
       "      <th>discovery</th>\n",
       "      <td>192.1910</td>\n",
       "      <td>58983.4</td>\n",
       "      <td>113455</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Multiple</th>\n",
       "      <th>Social</th>\n",
       "      <th>Facebook</th>\n",
       "      <td>105.5983</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11917</td>\n",
       "      <td>21888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bing Search</th>\n",
       "      <th>Bing</th>\n",
       "      <td>5.8760</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2308</td>\n",
       "      <td>13520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bath Sinks</th>\n",
       "      <th>Social</th>\n",
       "      <th>Facebook</th>\n",
       "      <td>257.5944</td>\n",
       "      <td>1.0</td>\n",
       "      <td>722</td>\n",
       "      <td>4439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Bath Faucets</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>Pinterest</th>\n",
       "      <td>141.7047</td>\n",
       "      <td>0.0</td>\n",
       "      <td>181</td>\n",
       "      <td>834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>136.3193</td>\n",
       "      <td>2.0</td>\n",
       "      <td>465</td>\n",
       "      <td>4927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Kitchen Sinks</th>\n",
       "      <th>Social</th>\n",
       "      <th>Facebook</th>\n",
       "      <td>267.8804</td>\n",
       "      <td>1.0</td>\n",
       "      <td>343</td>\n",
       "      <td>3344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bing Search</th>\n",
       "      <th>Bing</th>\n",
       "      <td>57.1451</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1507</td>\n",
       "      <td>10571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Showers/Baths</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>Pinterest</th>\n",
       "      <td>162.3006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5527</td>\n",
       "      <td>4287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>184.1421</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7447</td>\n",
       "      <td>16361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bing Search</th>\n",
       "      <th>Bing</th>\n",
       "      <td>105.9903</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4485</td>\n",
       "      <td>12392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Direct Display</th>\n",
       "      <th>discovery</th>\n",
       "      <td>70.6940</td>\n",
       "      <td>12.0</td>\n",
       "      <td>15798</td>\n",
       "      <td>2548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>National TV</th>\n",
       "      <th>scatter</th>\n",
       "      <td>215.7597</td>\n",
       "      <td>60298.8</td>\n",
       "      <td>141458</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>TikTok</th>\n",
       "      <td>221.9989</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2890</td>\n",
       "      <td>1411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Reddit</th>\n",
       "      <td>174.2360</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1245</td>\n",
       "      <td>1899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Direct Video</th>\n",
       "      <th>hulu.com</th>\n",
       "      <td>26.0803</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2439</td>\n",
       "      <td>3588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hbomax.com</th>\n",
       "      <td>13.6221</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1489</td>\n",
       "      <td>3581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lg - us</th>\n",
       "      <td>25.8478</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13414</td>\n",
       "      <td>3541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Kitchen Faucets</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Social</th>\n",
       "      <th>Pinterest</th>\n",
       "      <td>187.7129</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>217.7424</td>\n",
       "      <td>1.0</td>\n",
       "      <td>999</td>\n",
       "      <td>10037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           slope  min_spend  \\\n",
       "productcategory mediatype            platform                                 \n",
       "Other           Social               Pinterest           91.6273        0.0   \n",
       "                                     Facebook            82.2761        0.0   \n",
       "                Bing Search          Bing                 3.3551        1.0   \n",
       "                Programmatic Video   discovery           36.5491        0.0   \n",
       "                Programmatic Display discovery           27.4809        0.0   \n",
       "                                     padsquad            68.5564       11.0   \n",
       "                                     the trade desk     220.2158        0.0   \n",
       "                                     kargo              124.7605        1.0   \n",
       "Toilets         Social               Pinterest           69.8756        0.0   \n",
       "                                     Facebook           124.2930        4.0   \n",
       "                Bing Search          Bing               123.4833        0.0   \n",
       "                Direct Display       new york times_pg   51.9730        0.0   \n",
       "                                     uncrate             50.0850        0.0   \n",
       "                OOH                  big outdoor         65.7552      846.7   \n",
       "                                     clear channel       57.7892    49888.8   \n",
       "                National TV          discovery          192.1910    58983.4   \n",
       "Multiple        Social               Facebook           105.5983        3.0   \n",
       "                Bing Search          Bing                 5.8760        0.0   \n",
       "Bath Sinks      Social               Facebook           257.5944        1.0   \n",
       "Bath Faucets    Social               Pinterest          141.7047        0.0   \n",
       "                                     Facebook           136.3193        2.0   \n",
       "Kitchen Sinks   Social               Facebook           267.8804        1.0   \n",
       "                Bing Search          Bing                57.1451        0.0   \n",
       "Showers/Baths   Social               Pinterest          162.3006        0.0   \n",
       "                                     Facebook           184.1421        3.0   \n",
       "                Bing Search          Bing               105.9903        1.0   \n",
       "                Direct Display       discovery           70.6940       12.0   \n",
       "                National TV          scatter            215.7597    60298.8   \n",
       "                Social               TikTok             221.9989       21.0   \n",
       "                                     Reddit             174.2360        9.0   \n",
       "                Direct Video         hulu.com            26.0803       29.0   \n",
       "                                     hbomax.com          13.6221       19.0   \n",
       "                                     lg - us             25.8478        0.0   \n",
       "Kitchen Faucets Social               Pinterest          187.7129        0.0   \n",
       "                                     Facebook           217.7424        1.0   \n",
       "\n",
       "                                                        max_spend      n  \n",
       "productcategory mediatype            platform                             \n",
       "Other           Social               Pinterest                474   9646  \n",
       "                                     Facebook               10909  10796  \n",
       "                Bing Search          Bing                     541   5219  \n",
       "                Programmatic Video   discovery               2142   1692  \n",
       "                Programmatic Display discovery              22421     52  \n",
       "                                     padsquad                3729   2534  \n",
       "                                     the trade desk          4145   1575  \n",
       "                                     kargo                    564   1060  \n",
       "Toilets         Social               Pinterest               1252   5596  \n",
       "                                     Facebook                3983  18698  \n",
       "                Bing Search          Bing                    1632   6526  \n",
       "                Direct Display       new york times_pg       5192   3477  \n",
       "                                     uncrate                  957    411  \n",
       "                OOH                  big outdoor            23044      8  \n",
       "                                     clear channel          67539      8  \n",
       "                National TV          discovery             113455      9  \n",
       "Multiple        Social               Facebook               11917  21888  \n",
       "                Bing Search          Bing                    2308  13520  \n",
       "Bath Sinks      Social               Facebook                 722   4439  \n",
       "Bath Faucets    Social               Pinterest                181    834  \n",
       "                                     Facebook                 465   4927  \n",
       "Kitchen Sinks   Social               Facebook                 343   3344  \n",
       "                Bing Search          Bing                    1507  10571  \n",
       "Showers/Baths   Social               Pinterest               5527   4287  \n",
       "                                     Facebook                7447  16361  \n",
       "                Bing Search          Bing                    4485  12392  \n",
       "                Direct Display       discovery              15798   2548  \n",
       "                National TV          scatter               141458     15  \n",
       "                Social               TikTok                  2890   1411  \n",
       "                                     Reddit                  1245   1899  \n",
       "                Direct Video         hulu.com                2439   3588  \n",
       "                                     hbomax.com              1489   3581  \n",
       "                                     lg - us                13414   3541  \n",
       "Kitchen Faucets Social               Pinterest                 47    420  \n",
       "                                     Facebook                 999  10037  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "path = 'https://raw.githubusercontent.com/yurchisin/ODSC2024/refs/heads/main/data_files/'\n",
    "lm_coeff = pd.read_csv(path + 'lm_coeff.csv', index_col=['productcategory','mediatype','platform'])\n",
    "lm_coeff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Paste any code you want to test out\n",
    "# %pip install gurobipy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How I'd Model the Problem:\n",
    "Adding in an assumption that there are no free ads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Restricted license - for non-production use only - expires 2025-11-24\n",
      "Gurobi Optimizer version 11.0.0 build v11.0.0rc2 (mac64[rosetta2] - Darwin 23.6.0 23G80)\n",
      "\n",
      "CPU model: Apple M1\n",
      "Thread count: 8 physical cores, 8 logical processors, using up to 8 threads\n",
      "\n",
      "Optimize a model with 36 rows, 70 columns and 105 nonzeros\n",
      "Model fingerprint: 0x0a65bb6d\n",
      "Variable types: 35 continuous, 0 integer (0 binary)\n",
      "Semi-Variable types: 35 continuous, 0 integer\n",
      "Coefficient statistics:\n",
      "  Matrix range     [1e+00, 3e+02]\n",
      "  Objective range  [1e+00, 1e+00]\n",
      "  Bounds range     [1e+01, 1e+05]\n",
      "  RHS range        [4e+05, 4e+05]\n",
      "Presolve removed 35 rows and 35 columns\n",
      "Presolve time: 0.00s\n",
      "Presolved: 69 rows, 70 columns, 171 nonzeros\n",
      "Presolved model has 2 SOS constraint(s)\n",
      "Variable types: 35 continuous, 35 integer (35 binary)\n",
      "Found heuristic solution: objective 5.233005e+07\n",
      "\n",
      "Root relaxation: objective 6.439115e+07, 24 iterations, 0.00 seconds (0.00 work units)\n",
      "\n",
      "    Nodes    |    Current Node    |     Objective Bounds      |     Work\n",
      " Expl Unexpl |  Obj  Depth IntInf | Incumbent    BestBd   Gap | It/Node Time\n",
      "\n",
      "     0     0 6.4391e+07    0    1 5.2330e+07 6.4391e+07  23.0%     -    0s\n",
      "H    0     0                    6.341191e+07 6.4391e+07  1.54%     -    0s\n",
      "H    0     0                    6.408448e+07 6.4391e+07  0.48%     -    0s\n",
      "H    0     0                    6.408877e+07 6.4391e+07  0.47%     -    0s\n",
      "\n",
      "Cutting planes:\n",
      "  Flow cover: 1\n",
      "\n",
      "Explored 1 nodes (24 simplex iterations) in 0.09 seconds (0.00 work units)\n",
      "Thread count was 8 (of 8 available processors)\n",
      "\n",
      "Solution count 4: 6.40888e+07 6.40845e+07 6.34119e+07 5.233e+07 \n",
      "\n",
      "Optimal solution found (tolerance 1.00e-04)\n",
      "Best objective 6.408876906366e+07, best bound 6.409135414270e+07, gap 0.0040%\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "productcategory  mediatype             platform      \n",
       "Other            Social                Pinterest            484.0\n",
       "                                       Facebook           10919.0\n",
       "                 Programmatic Display  the trade desk      4155.0\n",
       "                                       kargo                574.0\n",
       "Toilets          Social                Facebook            3993.0\n",
       "                 Bing Search           Bing                1642.0\n",
       "                 OOH                   clear channel      49898.8\n",
       "                 National TV           discovery         113465.0\n",
       "Multiple         Social                Facebook           11927.0\n",
       "Bath Sinks       Social                Facebook             732.0\n",
       "Bath Faucets     Social                Pinterest            191.0\n",
       "                                       Facebook             475.0\n",
       "Kitchen Sinks    Social                Facebook             353.0\n",
       "Showers/Baths    Social                Pinterest           5537.0\n",
       "                                       Facebook            7457.0\n",
       "                 Bing Search           Bing                4495.0\n",
       "                 Direct Display        discovery          10909.2\n",
       "                 National TV           scatter           141468.0\n",
       "                 Social                TikTok              2900.0\n",
       "                                       Reddit              1255.0\n",
       "Kitchen Faucets  Social                Pinterest             57.0\n",
       "                                       Facebook            1009.0\n",
       "Name: dollars, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%pip install gurobipy\n",
    "\n",
    "import gurobipy as gp\n",
    "from gurobipy import GRB\n",
    "\n",
    "m = gp.Model(\"mmo\")\n",
    "\n",
    "no_free_ads = 10 # assume there is a positive min spend, calculate this how ever you want\n",
    "B = 373896 # average total weekly spend, use that as budget\n",
    "\n",
    "spend = m.addVars(lm_coeff.index, vtype=GRB.SEMICONT, lb = (lm_coeff.min_spend+no_free_ads), ub = (lm_coeff.max_spend+no_free_ads), name = 'spend')\n",
    "impressions = m.addVars(lm_coeff.index, name = 'impressions')\n",
    "\n",
    "lm_eqs = m.addConstrs((impressions[i,j,k] == lm_coeff.slope[i,j,k]*spend[i,j,k] for [i, j, k] in lm_coeff.index), name='lm_eqs')\n",
    "total_budget = m.addConstr(spend.sum() <= B, name = 'total_budget')\n",
    "\n",
    "m.setObjective(impressions.sum(), sense = GRB.MAXIMIZE)\n",
    "\n",
    "m.optimize()\n",
    "\n",
    "spend_values = pd.Series(m.getAttr('X', spend), name = \"dollars\", index = lm_coeff.index)\n",
    "spend_values[spend_values > 0]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "gurobi_ml",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
